/// <summary>
// CodeFx.MySqlProviders - .NET Providers for MySql using the framework CodeFx
// Laurent Morisseau
//
// This program is free software; you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation; either version 2 of the License, or
// (at your option) any later version.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with this program; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
//
// Laurent Morisseau
/// </summary>
/// <author><a href="mailto:laurent.morisseau@gmail.com">Laurent Morisseau</a></author>


namespace CodeFx.MySqlProviders
{
    using System;
    using System.Text;
    using System.Web;
    using System.Data;
    using System.ComponentModel;
    using MySql.Data.MySqlClient;
    using System.Collections;
    using CodeFx;
    using System.Web.Security;
    using CodeFx.Web;
	
    public static partial class Aspnet_membershipDTO
    {
        [DataObjectMethodAttribute(DataObjectMethodType.Update, false)]
        internal static void CustomUpdate(Aspnet_membershipDAL dataObject)
        {
            MembershipUser membershipuser = Membership.GetUser(dataObject.UserId);
            membershipuser.Comment = dataObject.Comment;
            membershipuser.Email = dataObject.Email;
            Membership.UpdateUser(membershipuser);
        }
        
        [DataObjectMethodAttribute(DataObjectMethodType.Select, false)]
        internal static Aspnet_membershipDAL CustomSelect(string username, bool isonline)
        {
            Aspnet_membershipDAL member = Aspnet_membershipDTO.SelectByFk_ApplicationId_UserName(Aspnet_applicationsDTO.GetApplicationId(Membership.Provider.ApplicationName), username);
            if (member == null)
                return null;
            if (isonline)
                Aspnet_usersDTO.Update_LastActivityDateWithPersistance(member.UserId , username);
            return member;
        }

        internal static string GetUserNameByEmail(uint ApplicationId, string Email)
        {
            string username = string.Empty ;

            MySqlConnection conn = ConnectionString.Current.OpenConnection;       
            MySqlCommand cmd = new MySqlCommand("SELECT  u.UserName FROM aspnet_users u, aspnet_membership m WHERE m.ApplicationId = ?ApplicationId  AND u.UserId = m.UserId AND (m.Email IS NULL or  m.Email = ?Email)", conn);
            cmd.Parameters.Add(new MySqlParameter("?ApplicationId", ApplicationId));
            cmd.Parameters.Add(new MySqlParameter("?Email", Email));
            MySqlDataReader dr = null;
            try
            {
                dr = cmd.ExecuteReader(CommandBehavior.SingleResult);
                if ((dr.Read()) && !(dr.IsDBNull(0)))
                    username = dr.GetString(0);
            }
            catch (Exception ex)
            {
                throw new DALException(cmd.CommandText , ex.InnerException);
            }
            finally
            {
                if (dr != null)
                    dr.Close();
                conn.Close();
            }
            return username;
        }

        public static Aspnet_membershipDAL SelectByFk_ApplicationId_UserName(uint ApplicationId, string UserName)
        {
            string command = "select m.ApplicationId,m.UserId,m.Password,m.PasswordFormat,m.PasswordSalt,m.MobilePin,m.Email,m.PasswordQuestion,m.PasswordAnswer,m.IsApproved,m.IsLockedOut,m.CreateDate,m.LastLoginDate,m.LastPasswordChangedDate,m.LastLockedOutDate,m.FailedPasswordAttemptCount,m.FailedPasswordAttemptWindowStart,m.FailedPasswordAnswerAttemptCount,m.FailedPasswordAnswerAttemptWindowStart,m.Comment from aspnet_membership m inner join aspnet_users u on (u.UserId = m.UserId) where m.ApplicationId =?ApplicationId and u.Username=?UserName";

            MySqlConnection conn = ConnectionString.Current.OpenConnection;       
            MySqlCommand cmd = new MySqlCommand(command, conn);

            cmd.Parameters.Add(new MySqlParameter("?ApplicationId", ApplicationId));
            cmd.Parameters.Add(new MySqlParameter("?UserName", UserName));
            try
            {
                return Select(cmd);
            }
            finally{
                conn.Close();
            }
        }

        internal static bool ExistMultipleEmail(uint ApplicationId, uint UserId, string Email)
        {
            bool exist;
            string command = "select 1 from aspnet_membership where UserId <> ?UserId and ApplicationId = ?ApplicationId and Email=?Email";

            MySqlConnection conn = ConnectionString.Current.OpenConnection;       
            MySqlCommand cmd = new MySqlCommand(command, conn);

            cmd.Parameters.Add(new MySqlParameter("?UserId", UserId));
            cmd.Parameters.Add(new MySqlParameter("?ApplicationId", ApplicationId));
            cmd.Parameters.Add(new MySqlParameter("?Email", Email));
            MySqlDataReader dr = null;
            try
            {
                dr = cmd.ExecuteReader();
                exist = dr.Read();
            }
            catch (Exception ex)
            {
                throw new DALException(cmd.CommandText, ex.InnerException);
            }
            finally
            {
                if (dr != null)
                    dr.Close();
                conn.Close();
            }
            return exist;
        }

        internal static void Update_LastLoginDate(Aspnet_membershipDAL dataObject)
        {
            if (dataObject == null)
                return;
            MySqlCommand cmd = null;

            MySqlConnection conn = ConnectionString.Current.OpenConnection;
            cmd = new MySqlCommand("update aspnet_membership set LastLoginDate = ?LastLoginDate where UserId = ?UserId", conn);
            cmd.Parameters.Add(new MySqlParameter("?LastLoginDate", DateTime.UtcNow));
            cmd.Parameters.Add(new MySqlParameter("?UserId", dataObject.UserId));
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new DALException(cmd.CommandText, ex.InnerException);
            }
            finally
            {
                conn.Close();
            }
        }

        internal static object GetNumberOfUsersOnline(uint ApplicationId, DateTime DateActive)
        {
            MySqlCommand cmd = null;

            MySqlConnection conn = ConnectionString.Current.OpenConnection;
            cmd = new MySqlCommand("SELECT COUNT(*) FROM aspnet_users u, aspnet_membership m WHERE LastActivityDate > ?DateActive AND u.ApplicationId = ?ApplicationId AND u.UserId = m.UserId", conn);

            cmd.Parameters.Add(new MySqlParameter("?ApplicationId", ApplicationId));
            cmd.Parameters.Add(new MySqlParameter("?DateActive", DateActive));
            try
            {
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new DALException(cmd.CommandText, ex.InnerException);
            }
            finally
            {
                conn.Close();
            }
        }

        internal static MembershipUserCollection GetAllUsers(string Name, uint ApplicationId, long lbound, long ubound)
        {

            MySqlConnection conn = ConnectionString.Current.OpenConnection;       
            MySqlCommand cmd = new MySqlCommand("SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved, m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate, u.UserId, m.IsLockedOut, m.LastLockedoutDate FROM   aspnet_membership m, aspnet_users u WHERE  u.UserId = m.UserId and  u.ApplicationId = ?ApplicationId ORDER BY u.UserName limit ?lbound,?ubound", conn);
            cmd.Parameters.Add(new MySqlParameter("?ApplicationId", ApplicationId));
            cmd.Parameters.Add(new MySqlParameter("?lbound", lbound));
            cmd.Parameters.Add(new MySqlParameter("?ubound", ubound));
            MembershipUserCollection al = null;
            MySqlDataReader dr = null;
            try
            {
                dr = cmd.ExecuteReader();
                if (dr.HasRows)
                    al = new MembershipUserCollection();
                while (dr.Read())
                {
                    string text1 = dr.IsDBNull(0) ? string.Empty : dr.GetString(0);
                    string text2 = dr.IsDBNull(1) ? string.Empty : dr.GetString(1);
                    string text3 = dr.IsDBNull(2) ? string.Empty : dr.GetString(2);
                    string text4 = dr.IsDBNull(3) ? string.Empty : dr.GetString(3);
                    bool flag1 = dr.GetBoolean(4);
                    DateTime time1 = dr.GetDateTime(5).ToLocalTime();
                    DateTime time2 = dr.GetDateTime(6).ToLocalTime();
                    DateTime time3 = dr.GetDateTime(7).ToLocalTime();
                    DateTime time4 = dr.GetDateTime(8).ToLocalTime();
                    uint guid1 = dr.GetUInt32(9);
                    bool flag2 = dr.GetBoolean(10);
                    DateTime time5 = dr.GetDateTime(11).ToLocalTime();
                    al.Add(new MembershipUser(Name, text1, guid1, text2, text3, text4, flag1, flag2, time1, time2, time3, time4, time5));
                }
            }
            catch (Exception ex)
            {
                throw new DALException(cmd.CommandText,ex.InnerException);
            }
            finally
            {
                if (dr != null)
                    dr.Close();
                conn.Close();
            }
            return al;
        }
        internal static MembershipUserCollection GetAllUsers(string Name, uint ApplicationId, string UserNameToMatch, long lbound, long ubound)
        {

            MySqlConnection conn = ConnectionString.Current.OpenConnection;       
            MySqlCommand cmd = new MySqlCommand("SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved, m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate, u.UserId, m.IsLockedOut, m.LastLockedoutDate FROM   aspnet_membership m, aspnet_users u WHERE  u.UserId = m.UserId and  u.ApplicationId = ?ApplicationId AND AND u.UserName LIKE ?UserNameToMatch ORDER BY u.UserName limit ?lbound,?ubound", conn);
            cmd.Parameters.Add(new MySqlParameter("?ApplicationId", ApplicationId));
            cmd.Parameters.Add(new MySqlParameter("?UserNameToMatch", UserNameToMatch));
            cmd.Parameters.Add(new MySqlParameter("?lbound", lbound));
            cmd.Parameters.Add(new MySqlParameter("?ubound", ubound));
            MembershipUserCollection al = null;
            MySqlDataReader dr = null;
            try
            {
                dr = cmd.ExecuteReader();
                if (dr.HasRows)
                    al = new MembershipUserCollection();
                while (dr.Read())
                {
                    string text1 = dr.IsDBNull(0) ? string.Empty : dr.GetString(0);
                    string text2 = dr.IsDBNull(1) ? string.Empty : dr.GetString(1);
                    string text3 = dr.IsDBNull(2) ? string.Empty : dr.GetString(2);
                    string text4 = dr.IsDBNull(3) ? string.Empty : dr.GetString(3);
                    bool flag1 = dr.GetBoolean(4);
                    DateTime time1 = dr.GetDateTime(5).ToLocalTime();
                    DateTime time2 = dr.GetDateTime(6).ToLocalTime();
                    DateTime time3 = dr.GetDateTime(7).ToLocalTime();
                    DateTime time4 = dr.GetDateTime(8).ToLocalTime();
                    uint guid1 = dr.GetUInt32(9); 
                    bool flag2 = dr.GetBoolean(10);
                    DateTime time5 = dr.GetDateTime(11).ToLocalTime();
                    al.Add(new MembershipUser(Name, text1, guid1, text2, text3, text4, flag1, flag2, time1, time2, time3, time4, time5));
                }
            }
            catch (Exception ex)
            {
                throw new DALException(cmd.CommandText, ex.InnerException);
            }
            finally
            {
                if (dr != null)
                    dr.Close();
                conn.Close();
            }
            return al;
        }
        internal static MembershipUserCollection GetAllUsersByEmail(string Name, uint ApplicationId, string EmailToMatch, long lbound, long ubound)
        {

            MySqlConnection conn = ConnectionString.Current.OpenConnection;       
            MySqlCommand cmd = new MySqlCommand("SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved, m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate, u.UserId, m.IsLockedOut, m.LastLockedoutDate FROM   aspnet_membership m, aspnet_users u WHERE  u.UserId = m.UserId and  u.ApplicationId = ?ApplicationId AND (?EmailToMatch is null or m.Email LIKE ?EmailToMatch) ORDER BY u.UserName limit ?lbound,?ubound", conn);
            cmd.Parameters.Add(new MySqlParameter("?ApplicationId", ApplicationId));
            cmd.Parameters.Add(new MySqlParameter("?EmailToMatch", EmailToMatch));
            cmd.Parameters.Add(new MySqlParameter("?lbound", lbound));
            cmd.Parameters.Add(new MySqlParameter("?ubound", ubound));
            MembershipUserCollection al = null;
            MySqlDataReader dr = null;
            try
            {
                dr = cmd.ExecuteReader();
                if (dr.HasRows)
                    al = new MembershipUserCollection();
                while (dr.Read())
                {
                    string text1 = dr.IsDBNull(0) ? string.Empty : dr.GetString(0);
                    string text2 = dr.IsDBNull(1) ? string.Empty : dr.GetString(1);
                    string text3 = dr.IsDBNull(2) ? string.Empty : dr.GetString(2);
                    string text4 = dr.IsDBNull(3) ? string.Empty : dr.GetString(3);
                    bool flag1 = dr.GetBoolean(4);
                    DateTime time1 = dr.GetDateTime(5).ToLocalTime();
                    DateTime time2 = dr.GetDateTime(6).ToLocalTime();
                    DateTime time3 = dr.GetDateTime(7).ToLocalTime();
                    DateTime time4 = dr.GetDateTime(8).ToLocalTime();
                    uint guid1 = dr.GetUInt32(9); 
                    bool flag2 = dr.GetBoolean(10);
                    DateTime time5 = dr.GetDateTime(11).ToLocalTime();
                    al.Add(new MembershipUser(Name, text1, guid1, text2, text3, text4, flag1, flag2, time1, time2, time3, time4, time5));
                }
            }
            catch (Exception ex)
            {
                throw new DALException(cmd.CommandText, ex.InnerException);
            }
            finally
            {
                if (dr != null)
                    dr.Close();
                conn.Close();
            }
            return al;
        }

        internal static object CountAllUsers(uint ApplicationId)
        {

            MySqlConnection conn = ConnectionString.Current.OpenConnection; 
            MySqlCommand cmd = new MySqlCommand("SELECT count(*) FROM   aspnet_membership m, aspnet_users u WHERE  u.UserId = m.UserId and  u.ApplicationId = ?ApplicationId ", conn);

            cmd.Parameters.Add(new MySqlParameter("?ApplicationId", ApplicationId));
            try
            {
                return cmd.ExecuteScalar();
            }
            finally
            {
                conn.Close();
            }
        }

        internal static object CountAllUsers(uint ApplicationId, string UserNameToMatch)
        {

            MySqlConnection conn = ConnectionString.Current.OpenConnection; 
            MySqlCommand cmd = new MySqlCommand("SELECT count(*) FROM   aspnet_membership m, aspnet_users u WHERE  u.UserId = m.UserId and  u.ApplicationId = ?ApplicationId AND u.UserName LIKE ?UserNameToMatch ", conn);

            cmd.Parameters.Add(new MySqlParameter("?ApplicationId", ApplicationId));
            cmd.Parameters.Add(new MySqlParameter("?UserNameToMatch", UserNameToMatch));
            try
            {
                return cmd.ExecuteScalar();
            }
            finally
            {
                conn.Close();
            }
        }
        internal static object CountAllUsersByEmail(uint ApplicationId, string EmailToMatch)
        {

            MySqlConnection conn = ConnectionString.Current.OpenConnection; 
            MySqlCommand cmd = new MySqlCommand("SELECT count(*) FROM   aspnet_membership m, aspnet_users u WHERE  u.UserId = m.UserId and  u.ApplicationId = ?ApplicationId AND (?EmailToMatch is null or m.Email LIKE ?EmailToMatch) ", conn);

            cmd.Parameters.Add(new MySqlParameter("?ApplicationId", ApplicationId));
            cmd.Parameters.Add(new MySqlParameter("?EmailToMatch", EmailToMatch));
            try
            {
                return cmd.ExecuteScalar();
            }
            finally
            {
                conn.Close();
            }
        }

        internal static void SetPassword(string NewPassword, string PasswordFormat, string PasswordSalt, uint UserId)
        {

            MySqlConnection conn = ConnectionString.Current.OpenConnection; 
            MySqlCommand cmd = new MySqlCommand("UPDATE aspnet_membership SET Password = ?NewPassword, PasswordFormat = ?PasswordFormat, PasswordSalt = ?PasswordSalt, LastPasswordChangedDate = ?CurrentTimeUtc WHERE ?UserId = UserId", conn);

            cmd.Parameters.Add(new MySqlParameter("?NewPassword", NewPassword));
            cmd.Parameters.Add(new MySqlParameter("?PasswordFormat", PasswordFormat));
            cmd.Parameters.Add(new MySqlParameter("?PasswordSalt", PasswordSalt));
            cmd.Parameters.Add(new MySqlParameter("?UserId", UserId.ToString()));
            cmd.Parameters.Add(new MySqlParameter("?CurrentTimeUtc", DateTime.UtcNow));
            try
            {
                cmd.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        internal static void ChangePasswordQuestionAndAnswer(string NewPasswordQuestion, string NewPasswordAnswer, uint UserId)
        {

            MySqlConnection conn = ConnectionString.Current.OpenConnection; 
            MySqlCommand cmd = new MySqlCommand("UPDATE aspnet_membership SET PasswordQuestion = ?NewPasswordQuestion, PasswordAnswer = ?NewPasswordAnswer WHERE  UserId=?UserId", conn);

            cmd.Parameters.Add(new MySqlParameter("?NewPasswordQuestion", NewPasswordQuestion));
            cmd.Parameters.Add(new MySqlParameter("?NewPasswordAnswer", NewPasswordAnswer));
            cmd.Parameters.Add(new MySqlParameter("?UserId", UserId));
            try
            {
                cmd.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        internal static bool Exist(uint ApplicationId, string Email)
        {
            bool exist;
            string command = "select 1 from aspnet_membership where ApplicationId =?ApplicationId and Email=?Email";

            MySqlConnection conn = ConnectionString.Current.OpenConnection;       
            MySqlCommand cmd = new MySqlCommand(command, conn);

            cmd.Parameters.Add(new MySqlParameter("?ApplicationId", ApplicationId));
            cmd.Parameters.Add(new MySqlParameter("?Email", Email));
            MySqlDataReader dr = null;
            try
            {
                dr = cmd.ExecuteReader();
                exist = dr.Read();
            }
            catch (Exception ex)
            {
                throw new DALException(cmd.CommandText, ex.InnerException);
            }
            finally
            {
                if (dr != null)
                    dr.Close();
                conn.Close();
            }
            return exist;
        }
	}
}